package com.wlyy.bcwlw.common.export2excel.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import com.wlyy.bcwlw.common.export2excel.entity.TableCell;
import com.wlyy.bcwlw.common.utils.ExcelUtil;
import com.wlyy.bcwlw.common.utils.HttpUtils;

/**
 * 
 * @ClassName. Export2ExcelService
 * @Description. 导出服务
 * @author zhangyang
 * @date 2016年5月31日 上午9:00:56
 * @version V1.0
 */
@Service
public class Export2ExcelService {



	private HSSFWorkbook wb;


	public void exportTable(HttpServletResponse response, List<List<TableCell>> tableCellList) {
		
		wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, "sheet1");
		int i = 0;

		for (List<TableCell> tableRow : tableCellList) {
			HSSFRow row = sheet.createRow(i);

			int j = 0;
			int minRowSpan = tableRow.get(0).getRowSpan();

			for (TableCell tableCell : tableRow) {
				if (tableCell.getRowSpan() < minRowSpan) {
					minRowSpan = tableCell.getRowSpan();
				}
				while (hasReginMerged(sheet, i, j, i, j)) {
					j++;
				}
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(style);
				HSSFRichTextString str = new HSSFRichTextString(tableCell.getCellValue());
				cell.setCellValue(str);
				if (tableCell.getColSpan() != 1 || tableCell.getRowSpan() != 1) {
					
					CellRangeAddress cellRangeAddress = new CellRangeAddress(i, j, i + tableCell.getRowSpan() - 1, (short)(j + tableCell.getColSpan() -1) );
					
					sheet.addMergedRegion(cellRangeAddress);
				}
				j += tableCell.getColSpan();
			}
		    i += minRowSpan;
		}

		

	    response.reset();
	    response.setContentType("application/vnd.ms-excel");
	    // 输出Excel

    	try {
			wb.write(response.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void exportTable(HttpServletRequest request, HttpServletResponse response, List<List<TableCell>> tableCellList, String fileName) {
		
		wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, "sheet1");
		int i = 0;

		for (List<TableCell> tableRow : tableCellList) {
			HSSFRow row = sheet.createRow(i);

			int j = 0;
			int minRowSpan = tableRow.get(0).getRowSpan();

			for (TableCell tableCell : tableRow) {
				if (tableCell.getRowSpan() < minRowSpan) {
					minRowSpan = tableCell.getRowSpan();
				}
				while (hasReginMerged(sheet, i, j, i, j)) {
					j++;
				}
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(style);
				HSSFRichTextString str = new HSSFRichTextString(tableCell.getCellValue());
				cell.setCellValue(str);
				if (tableCell.getColSpan() != 1 || tableCell.getRowSpan() != 1) {
					
					CellRangeAddress cellRangeAddress = new CellRangeAddress(i, j, i + tableCell.getRowSpan() - 1, (short)(j + tableCell.getColSpan() -1) );
					
					sheet.addMergedRegion(cellRangeAddress);
				}
				j += tableCell.getColSpan();
			}
		    i += minRowSpan;
		}

		

	    response.reset();
	    response.setContentType("application/vnd.ms-excel");
	    response.setHeader("Content-Disposition",
				"attachment;filename=" + HttpUtils.convert(request, fileName));
	    // 输出Excel

    	try {
			wb.write(response.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void exportTableWithStyle(HttpServletRequest request, HttpServletResponse response, List<List<TableCell>> tableCellList, String fileName) {
		
		wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFSheet sheet = wb.createSheet();
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		wb.setSheetName(0, "sheet1");
		int i = 0;

		for (List<TableCell> tableRow : tableCellList) {
			HSSFRow row = sheet.createRow(i);

			int j = 0;
			int minRowSpan = tableRow.get(0).getRowSpan();

			for (TableCell tableCell : tableRow) {
				if (tableCell.getRowSpan() < minRowSpan) {
					minRowSpan = tableCell.getRowSpan();
				}
				while (hasReginMerged(sheet, i, j, i, j)) {
					j++;
				}
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(style);
				HSSFRichTextString str = new HSSFRichTextString(tableCell.getCellValue());
				cell.setCellValue(str);
				
				if (tableCell.getColSpan() != 1 || tableCell.getRowSpan() != 1) {
					
					CellRangeAddress cellRangeAddress = new CellRangeAddress(i, j, i + tableCell.getRowSpan() - 1, (short)(j + tableCell.getColSpan() -1) );
					
					sheet.addMergedRegion(cellRangeAddress);
				}
				j += tableCell.getColSpan();
				
//				if( str != null && StringUtils.isNotBlank(str.toString())) {
					cell.setCellStyle(cellStyle);
//				}
			}
		    i += minRowSpan;
		}

		

	    response.reset();
	    response.setContentType("application/vnd.ms-excel");
	    response.setHeader("Content-Disposition",
				"attachment;filename=" + HttpUtils.convert(request, fileName));
	    // 输出Excel

    	try {
			wb.write(response.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void exportTable(HttpServletResponse response, List<List<TableCell>> tableCellList, String path) throws Exception {
		
		wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, "sheet1");
		int i = 0;

		for (List<TableCell> tableRow : tableCellList) {
			HSSFRow row = sheet.createRow(i);

			int j = 0;
			int minRowSpan = tableRow.get(0).getRowSpan();

			for (TableCell tableCell : tableRow) {
				if (tableCell.getRowSpan() < minRowSpan) {
					minRowSpan = tableCell.getRowSpan();
				}
				while (hasReginMerged(sheet, i, j, i, j)) {
					j++;
				}
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(style);
				HSSFRichTextString str = new HSSFRichTextString(tableCell.getCellValue());
				cell.setCellValue(str);
				if (tableCell.getColSpan() != 1 || tableCell.getRowSpan() != 1) {
					
					CellRangeAddress cellRangeAddress = new CellRangeAddress(i, j, i + tableCell.getRowSpan() - 1, (short)(j + tableCell.getColSpan() -1) );
					
					sheet.addMergedRegion(cellRangeAddress);
				}
				j += tableCell.getColSpan();
			}
		    i += minRowSpan;
		}

	    response.reset();
	    response.setContentType("application/vnd.ms-excel");
	    // 输出Excel

		File excelFile = new File(path);
		OutputStream output = new FileOutputStream(excelFile);
	    
		ExcelUtil.exportWorkBook(wb, output);
		if( output != null ) {
			output.close();
		}
	}
	
	public void exportTable(HttpServletRequest request, HttpServletResponse response, Map<String, List<List<TableCell>>> excelTableCellList, String fileName) throws Exception {
		int n = 0 ;
		wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		for( Entry<String, List<List<TableCell>>> entry : excelTableCellList.entrySet() ) {
			HSSFSheet sheet = wb.createSheet();
			wb.setSheetName(n, entry.getKey());
			int i = 0;
			
			List<List<TableCell>> tableCellList = excelTableCellList.get(entry.getKey());
			
			for (List<TableCell> tableRow : tableCellList) {
				HSSFRow row = sheet.createRow(i);

				int j = 0;
				int minRowSpan = tableRow.get(0).getRowSpan();

				for (TableCell tableCell : tableRow) {
					if (tableCell.getRowSpan() < minRowSpan) {
						minRowSpan = tableCell.getRowSpan();
					}
					while (hasReginMerged(sheet, i, j, i, j)) {
						j++;
					}
					HSSFCell cell = row.createCell(j);
					cell.setCellStyle(style);
					HSSFRichTextString str = new HSSFRichTextString(tableCell.getCellValue());
					cell.setCellValue(str);
					if (tableCell.getColSpan() != 1 || tableCell.getRowSpan() != 1) {
						
						CellRangeAddress cellRangeAddress = new CellRangeAddress(i, j, i + tableCell.getRowSpan() - 1, (short)(j + tableCell.getColSpan() -1) );
						
						sheet.addMergedRegion(cellRangeAddress);
					}
					j += tableCell.getColSpan();
				}
			    i += minRowSpan;
			}
			n++;
		}
		

		response.reset();
	    response.setContentType("application/vnd.ms-excel");
	    response.setHeader("Content-Disposition",
				"attachment;filename=" + HttpUtils.convert(request, fileName));
	    // 输出Excel

    	try {
			wb.write(response.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	private boolean hasReginMerged (HSSFSheet sheet, int rowFrom, int colFrom, int rowTo, int colTo) {
	      for (int row = rowFrom; row <= rowTo; row++) { 
	            for (int col = colFrom; col <= colTo; col++){ 
	                for (int i = 0; i < sheet.getNumMergedRegions(); i++) { 
	                	CellRangeAddress r = sheet.getMergedRegion(i); 
	                    if (r.isInRange(row, col)) { 
	                        return true; 
	                    } 
	                } 
	            } 
	        } 
	        return false; 

	}
}
